#!/usr/bin/env bash
cd `dirname $0`
source ../../../config.sh
source ../../basic/app/app_table_data_source.sh

exec_dir student_statistics_info

HIVE_DB=app
HIVE_TABLE=student_statistics_info
TARGET_TABLE=student_statistics_info
MODEL_DB="student_excellent,basic_ecard_consume_record,basic_student_info,
basic_network_record,basic_semester_info,student_psychological_test_record,
student_physical_test_record,student_graduate_employment_record,
student_job_orientation,semester_year_report"


function create_table(){
 hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
    hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
    hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
	 semester_year  String comment'学年 YYYY-YYYY',
	 total_num int comment'学生总数'，
	 excellent_student int comment'优秀学生数量',
	 consum_avg FLOAT comment'日均消费金额',
	 network_duration_avg int comment'日均上网时长',
	 psychology_quality FLOAT comment'心理素质测评分',
	 body_test FLOAT comment'体测平均成绩',
	 job_rate  FLOAT comment'平均就业率',
	 satisfied_rate  FLOAT comment'工作满意度',
	 relevant_rate  FLOAT comment'专业相关度',
	 create_time   String comment'创建时间'
	 )COMMENT '学生概况统计'
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"
    fn_log "创建表——学生概况统计：${HIVE_DB}.${HIVE_TABLE}"
}
function import_table() {
    hive -e "
         INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		 select
			'${SEMESTER_YEAR}' as semester_year,
			a.total_num as total_num,
			b.excellent_student as excellent_student,
			c.consum_avg as consum_avg,
			d.network_duration_avg as network_duration_avg,
			e.psychology_quality as psychology_quality,
			f.body_test as body_test,
			g.job_rate as job_rate,
			h.satisfied_rate as satisfied_rate,
			h.relevant_rate as relevant_rate,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			from
			(
			select
			count(stu.code) as total_num,
			sem.semester_year as semester_year
            FROM model.basic_student_info stu,model.basic_semester_info sem
            where sem.semester_year='${SEMESTER_YEAR}'
            group by sem.semester_year
			)a,
			(
			select
			count(b.code) as excellent_student
			from
			model.student_excellent b
			where b.semester_year='${SEMESTER_YEAR}'
			)b,
			(
			select
			cast(t1.fee as decimal(9,2)) as consum_avg
			from model.basic_ecard_consume_record t1
        left join model.basic_student_info t2 on t1.code=t2.code
			where t1.semester_year='${SEMESTER_YEAR}'
			)c,
			(
			select
			cast((sum(d.workday_netPlay_hours+d.weekend_netPlay_hours) /
                           count(0)
                           ) as int) as network_duration_avg
			from
			(
			select
			SUM(case when
            pmod(datediff(SUBSTR(on_line_time,1,10), '2019-01-01') + 2, 7)!=0
            or pmod(datediff(SUBSTR(on_line_time,1,10), '2019-01-01') + 2, 7)!=6
                then time_long else 0 end) as workday_netPlay_hours,
            SUM(case when pmod(datediff(SUBSTR(on_line_time,1,10), '2019-01-01') + 2, 7)=0
            or pmod(datediff(SUBSTR(on_line_time,1,10), '2019-01-01') + 2, 7)=6
                then time_long else 0 end) as weekend_netPlay_hours
                FROM
            model.basic_network_record,model.basic_semester_info
            WHERE on_line_time BETWEEN begin_time and end_time and d.semester_year='${SEMESTER_YEAR}'
			 )d
			)d,
			(
			select
			nvl(avg(cast(e.score as decimal(9,2))),0)  as psychology_quality
			from
			model.student_psychological_test_record e
			where e.semester_year = '${SEMESTER_YEAR}'
			)e,
			(
			select
			nvl(avg(cast(f.score as decimal(9,2))),0) as body_test
			from
			model.student_physical_test_record f
			where f.semester_year = '${SEMESTER_YEAR}'
			)f,
			(
			select
			avg(cast(nvl(employment_num/graduation_num,0) as decimal(9,2))) as job_rate
			from
			(
			SELECT b.major_code as major_code,b.semester_year,count(1) as graduation_num from model.student_graduate_employment_record a left join app.basic_semester_student_info b on
                a.code=b.code GROUP BY b.major_code,b.semester_year ) t1
			left join
			(
			SELECT major_code,semester_year,count(1) as employment_num from model.student_job_orientation b
                GROUP BY major_code,semester_year) t2
			on t1.major_code=t2.major_code and t1.semester_year=t2.semester_year
			and  t1.semester_year='${SEMESTER_YEAR}'
			)g,
			(
			select
			h.satisfied_rate as satisfied_rate,
			h.relevant_rate as relevant_rate
			from 
			model.semester_year_report h
			where h.semester_year='${SEMESTER_YEAR}'
			)h
			 "
		 fn_log "导入数据 —— 师资概况统计：${HIVE_DB}.${HIVE_TABLE}" 
	}
function export_table() {
     clear_mysql_data "TRUNCATE TABLE ${TARGET_TABLE};"
    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'semester_year,psychology_quality,body_test,job_rate,satisfied_rate,relevant_rate,create_time'
    fn_log "导出数据--课程概况统计:${HIVE_DB}.${TARGET_TABLE}"
}

function getYearData(){
    vDate=`date +%Y`
    let vDate+=0;
    for((i=1;i<=3;i++));
    do
        let NOW_YEAR=vDate-i+1
        let PRE_YEAR=vDate-i
        create_table
        import_table
        export_table
    done
}

if [ $# == 1 ]
    then
    getYearData
    data_cleaning
    finish
else
    getYearData
    finish
fi